package com.sh.data.engine.domain.shims.oracle.manager;

import com.google.common.collect.Maps;
import com.sh.data.engine.domain.shims.db.DbOptions;
import com.sh.data.engine.domain.shims.db.GenericDbManager;
import com.sh.data.engine.domain.shims.db.model.FieldInfoDomain;
import com.sh.data.engine.domain.shims.db.model.PreviewDataDomain;
import com.sh.data.engine.domain.shims.db.model.TableInfoDomain;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;

import java.sql.*;
import java.util.*;

/**
 * @author: mengzheng.mhc
 * @date: 2024/6/6 9:39
 */
public class OracleManager extends GenericDbManager {

    public OracleManager(DbOptions options) {
        super(options);
    }

    static {
        String driverClass = "oracle.jdbc.driver.OracleDriver";
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("Could not load db driver class: " + driverClass);
        }
    }

    @Override
    public List<FieldInfoDomain> getFieldList(String dbName, String schema, String tableName)
        throws SQLException {
        List<FieldInfoDomain> list = new LinkedList<>();

        try (Connection conn = getConnection()) {

            DatabaseMetaData metaData = conn.getMetaData();
            String catalog = conn.getCatalog();

            String escape = metaData.getSearchStringEscape();

            String newTableName = tableName;
            newTableName = newTableName.replace("%", (escape + "%"));
            newTableName = newTableName.replace("_", (escape + "_"));
            if (StringUtils.isBlank(schema)) {
                schema = conn.getSchema();
            }
            String newSchema = schema;
            if (StringUtils.isNotBlank(newSchema)) {
                newSchema = newSchema.replace("%", (escape + "%"));
                newSchema = newSchema.replace("_", (escape + "_"));
            }

            try (ResultSet rs = metaData.getColumns(catalog, newSchema, newTableName, null)) {
                while (rs != null && rs.next()) {
                    int dataType = rs.getInt("DATA_TYPE");
                    String typeName = rs.getString("TYPE_NAME");
                    String columnName = rs.getString("COLUMN_NAME");
                    int columnSize = rs.getInt("COLUMN_SIZE");
                    int decimalDigits = rs.getInt("DECIMAL_DIGITS");
                    // 字段是否可为NULL，0-NO  1-YES
                    int nullable = rs.getInt("NULLABLE");
                    String remarks = rs.getString("REMARKS");

                    /** 拼接decimal,varchar,char */
                    if (typeName.equalsIgnoreCase("NUMBER")) {
                        typeName = typeName + "(" + columnSize + "," + decimalDigits + ")";
                    }
                    if (typeName.equalsIgnoreCase("VARCHAR2")
                        || typeName.equalsIgnoreCase("NVARCHAR2")
                        || typeName.equalsIgnoreCase("NCHAR")
                        || typeName.equalsIgnoreCase("char")) {
                        typeName = typeName + "(" + columnSize + ")";
                    }

                    FieldInfoDomain fieldInfo =
                        FieldInfoDomain.builder()
                            .dataType(dataType)
                            .fieldName(columnName)
                            .fieldType(typeName)
                            .columnSize(columnSize)
                            .decimalDigits(decimalDigits)
                            .isNullable(nullable == 1)
                            .comment(remarks)
                            .build();
                    list.add(fieldInfo);
                }
            }

            if (CollectionUtils.isEmpty(list)) {
                return list;
            }

            try (ResultSet rs = metaData.getPrimaryKeys(catalog, schema, tableName)) {
                while (rs != null && rs.next()) {
                    String columnName = rs.getString("COLUMN_NAME");
                    Optional<FieldInfoDomain> optional =
                        list.stream()
                            .filter(e -> StringUtils.equals(e.getFieldName(), columnName))
                            .findFirst();
                    if (optional.isPresent()) {
                        optional.get().setPk(true);
                    }
                }
            }
        }

        Map<String, String> allColDes = getAllColDes(schema, tableName);
        for (FieldInfoDomain fieldInfoDomain : list) {
            String fieldName = fieldInfoDomain.getFieldName();
            String comment = allColDes.get(fieldName);
            fieldInfoDomain.setComment(comment);
        }

        return list;
    }

    @Override
    public String getQueryVersionSql() {
        return "SELECT * FROM v$version where rownum <= 1";
    }

    public Map<String, String> getAllColDes(String schema, String tableName) throws SQLException {

        String sql =
            "select COLUMN_NAME,COMMENTS from all_col_comments where OWNER = ? and TABLE_NAME = ? ";

        Map<String, String> map = Maps.newHashMap();
        try (Connection conn = getConnection();
             PreparedStatement pstmt = createPreparedStatement(conn, sql, schema, tableName);
             ResultSet resultSet = pstmt.executeQuery()) {
            while (resultSet != null && resultSet.next()) {
                getAllColDesMap(resultSet, map);
            }
        }
        return map;
    }

    private void getAllColDesMap(ResultSet rs, Map<String, String> map) throws SQLException {
        String columnName = rs.getString("COLUMN_NAME");
        String comments = rs.getString("COMMENTS");
        map.put(columnName, comments);
    }

    @Override
    public List<TableInfoDomain> getTableList(String dbName) throws SQLException {
        String sql =
            "select o.owner,o.object_name,o.object_id,o.object_type,o.last_ddl_time,c.comments "
                + "from all_objects o "
                + "LEFT JOIN all_tab_comments c on o.owner = c.owner and o.object_name = c.table_name "
                + "where o.object_type = 'TABLE' and o.owner not in ('ANONYMOUS','APEX_030200','APEX_040000','APEX_PUBLIC_USER','APPQOSSYS','BI','CTXSYS','DBSNMP','DIP','EXFSYS','FLOWS_FILES','HR','IX','MDDATA','MDSYS','MGMT_VIEW','OE','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS','OWBSYS_AUDIT','PM','SCOTT','SH','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SYS','SYSMAN','SYSTEM','WMSYS','XDB','XS$NULL')";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection conn = getConnection();
             PreparedStatement pstmt = createPreparedStatement(conn, sql);
             ResultSet resultSet = pstmt.executeQuery()) {
            while (resultSet != null && resultSet.next()) {
                TableInfoDomain tableInfo = getTableDomain(dbName, resultSet);
                list.add(tableInfo);
            }
        }
        return list;
    }

    @Override
    public List<TableInfoDomain> getTableList(String dbName, String schema) throws SQLException {
        String sql =
            "select o.owner,o.object_name,o.object_id,o.object_type,o.last_ddl_time,c.comments "
                + "from all_objects o "
                + "LEFT JOIN all_tab_comments c on o.owner = c.owner and o.object_name = c.table_name "
                + "where o.object_type = 'TABLE' and o.owner = ?";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection conn = getConnection();
             PreparedStatement pstmt = createPreparedStatement(conn, sql, schema);
             ResultSet resultSet = pstmt.executeQuery()) {
            while (resultSet != null && resultSet.next()) {
                TableInfoDomain tableInfo = getTableDomain(dbName, resultSet);
                list.add(tableInfo);
            }
        }
        return list;
    }

    private static TableInfoDomain getTableDomain(String dbName, ResultSet rs) throws SQLException {
        ResultSetMetaData meta = rs.getMetaData();
        Set<String> set = new HashSet<>();
        for (int index = 1; index <= meta.getColumnCount(); index++) {
            set.add(meta.getColumnLabel(index));
        }
        TableInfoDomain entity = new TableInfoDomain();
        entity.setDbName(dbName);
        entity.setSchema(rs.getString("owner"));
        //    entity.setTableName(rs.getString("object_name"));
        entity.setTableName(rs.getString("object_name"));
        entity.setTblId(rs.getLong("object_id"));
        entity.setLastDdlTime(rs.getDate("last_ddl_time").getTime());
        entity.setTableComment(rs.getString("comments"));

        final String objectType = rs.getString("object_type");

        TableInfoDomain.TblType tblType = null;
        switch (objectType) {
            case "TABLE":
                tblType = TableInfoDomain.TblType.MANAGED_TABLE;
                break;
            case "VIEW":
                tblType = TableInfoDomain.TblType.VIEW;
                break;
        }
        entity.setTblType(tblType);

        return entity;
    }

    @Override
    public List<TableInfoDomain> getViewList(String dbName) throws SQLException {
        String sql =
            "select o.owner,o.object_name,o.object_id,o.object_type,o.last_ddl_time,c.comments "
                + "from all_objects o "
                + "LEFT JOIN all_tab_comments c on o.owner = c.owner and o.object_name = c.table_name "
                + "where o.object_type ='VIEW' and o.owner not in ('ANONYMOUS','APEX_030200','APEX_040000','APEX_PUBLIC_USER','APPQOSSYS','BI','CTXSYS','DBSNMP','DIP','EXFSYS','FLOWS_FILES','HR','IX','MDDATA','MDSYS','MGMT_VIEW','OE','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS','OWBSYS_AUDIT','PM','SCOTT','SH','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SYS','SYSMAN','SYSTEM','WMSYS','XDB','XS$NULL')";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection conn = getConnection();
             PreparedStatement pstmt = createPreparedStatement(conn, sql);
             ResultSet resultSet = pstmt.executeQuery()) {
            while (resultSet != null && resultSet.next()) {
                TableInfoDomain tableInfo = getTableDomain(dbName, resultSet);
                list.add(tableInfo);
            }
        }
        return list;
    }

    @Override
    public List<TableInfoDomain> getViewList(String dbName, String schema) throws SQLException {
        String sql =
            "select o.owner,o.object_name,o.object_id,o.object_type,o.last_ddl_time,c.comments "
                + "from all_objects o "
                + "LEFT JOIN all_tab_comments c on o.owner = c.owner and o.object_name = c.table_name "
                + "where o.object_type ='VIEW' and o.owner = ?";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection conn = getConnection();
             PreparedStatement pstmt = createPreparedStatement(conn, sql, schema);
             ResultSet resultSet = pstmt.executeQuery()) {
            while (resultSet != null && resultSet.next()) {
                TableInfoDomain tableInfo = getTableDomain(dbName, resultSet);
                list.add(tableInfo);
            }
        }
        return list;
    }

    @Override
    public List<TableInfoDomain> getTableAndViewList(String dbName) throws SQLException {
        String sql =
            "select o.owner,o.object_name,o.object_id,o.object_type,o.last_ddl_time,c.comments "
                + "from all_objects o "
                + "LEFT JOIN all_tab_comments c on o.owner = c.owner and o.object_name = c.table_name "
                + "where o.object_type in ('TABLE','VIEW') and o.owner not in ('ANONYMOUS','APEX_030200','APEX_040000','APEX_PUBLIC_USER','APPQOSSYS','BI','CTXSYS','DBSNMP','DIP','EXFSYS','FLOWS_FILES','HR','IX','MDDATA','MDSYS','MGMT_VIEW','OE','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS','OWBSYS_AUDIT','PM','SCOTT','SH','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SYS','SYSMAN','SYSTEM','WMSYS','XDB','XS$NULL')";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection conn = getConnection();
             PreparedStatement pstmt = createPreparedStatement(conn, sql);
             ResultSet resultSet = pstmt.executeQuery()) {
            while (resultSet != null && resultSet.next()) {
                TableInfoDomain tableInfo = getTableDomain(dbName, resultSet);
                list.add(tableInfo);
            }
        }
        return list;
    }

    @Override
    public List<TableInfoDomain> getTableAndViewList(String dbName, String schema)
        throws SQLException {
        if (StringUtils.isBlank(schema)) {
            return getTableAndViewList(dbName);
        }
        String sql =
            "select o.owner,o.object_name,o.object_id,o.object_type,o.last_ddl_time,c.comments "
                + "from all_objects o "
                + "LEFT JOIN all_tab_comments c on o.owner = c.owner and o.object_name = c.table_name "
                + "where o.object_type in ('TABLE','VIEW') and o.owner = ?";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection conn = getConnection();
             PreparedStatement pstmt = createPreparedStatement(conn, sql, schema);
             ResultSet resultSet = pstmt.executeQuery()) {
            while (resultSet != null && resultSet.next()) {
                TableInfoDomain tableInfo = getTableDomain(dbName, resultSet);
                list.add(tableInfo);
            }
        }
        return list;
    }

    @Override
    public TableInfoDomain getTableInfoByTableName(String dbName, String tableName)
        throws SQLException {

        if (StringUtils.contains(tableName, ".")) {
            tableName = StringUtils.substringAfter(tableName, ".");
        }

        String sql =
            "select o.owner,o.object_name,o.object_id,o.object_type,o.last_ddl_time,c.comments "
                + "from all_objects o "
                + "LEFT JOIN all_tab_comments c on o.owner = c.owner and o.object_name = c.table_name "
                + "where o.object_type in ('TABLE','VIEW') and o.owner not in ('ANONYMOUS','APEX_030200','APEX_040000','APEX_PUBLIC_USER','APPQOSSYS','BI','CTXSYS','DBSNMP','DIP','EXFSYS','FLOWS_FILES','HR','IX','MDDATA','MDSYS','MGMT_VIEW','OE','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS','OWBSYS_AUDIT','PM','SCOTT','SH','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SYS','SYSMAN','SYSTEM','WMSYS','XDB','XS$NULL') and o.object_name = ?";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection conn = getConnection();
             PreparedStatement pstmt = createPreparedStatement(conn, sql, tableName);
             ResultSet resultSet = pstmt.executeQuery()) {
            while (resultSet != null && resultSet.next()) {
                TableInfoDomain tableInfo = getTableDomain(dbName, resultSet);
                list.add(tableInfo);
            }
        }
        return list.isEmpty() ? null : list.get(0);
    }

    @Override
    public TableInfoDomain getTableInfoByTableName(String dbName, String schema, String tableName)
        throws SQLException {
        String sql =
            "select o.owner,o.object_name,o.object_id,o.object_type,o.last_ddl_time,c.comments "
                + "from all_objects o "
                + "LEFT JOIN all_tab_comments c on o.owner = c.owner and o.object_name = c.table_name "
                + "where o.object_type in ('TABLE','VIEW') and o.owner = ? and o.object_name = ?";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection conn = getConnection();
             PreparedStatement pstmt = createPreparedStatement(conn, sql, schema, tableName);
             ResultSet resultSet = pstmt.executeQuery()) {
            while (resultSet != null && resultSet.next()) {
                TableInfoDomain tableInfo = getTableDomain(dbName, resultSet);
                list.add(tableInfo);
            }
        }
        return list.isEmpty() ? null : list.get(0);
    }

    @Override
    protected String getPreviewDataSql(String tableName) {
        String sql = "SELECT * FROM " + tableName + " WHERE ROWNUM <= 10";
        return sql;
    }

    @Override
    public PreviewDataDomain previewData(String schema, String tableName) throws SQLException {
        String tName = escapeTableName(schema) + "." + escapeTableName(tableName);
        return previewData(tName);
    }

    @Override
    public String getQuerySchemaListSql() {
        String sql =
            "select username from all_users "
                + "where username not in ('ANONYMOUS','APEX_030200','APEX_040000','APEX_PUBLIC_USER','APPQOSSYS','BI','CTXSYS','DBSNMP','DIP','EXFSYS','FLOWS_FILES','HR','IX','MDDATA','MDSYS','MGMT_VIEW','OE','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS','OWBSYS_AUDIT','PM','SCOTT','SH','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SYS','SYSMAN','SYSTEM','WMSYS','XDB','XS$NULL') "
                + "order by username";
        return sql;
    }

    @Override
    public boolean alterFieldDesc(
        String dbName, String tableName, String fieldName, String fieldDes, boolean isPartition)
        throws SQLException {
        throw new SQLException("暂未实现");
    }

    public static void main(String[] args) throws SQLException {
        DbOptions dbOptions = new DbOptions();
        dbOptions.setUrl("jdbc:oracle:thin:@10.88.36.79:1522:xe");
        dbOptions.setUsername("system");
        dbOptions.setPassword("oracle");
        OracleManager oracleManager = new OracleManager(dbOptions);

        List<TableInfoDomain> tableList = oracleManager.getTableAndViewList("");

        for (TableInfoDomain tableInfoDomain : tableList) {
            System.out.println(tableInfoDomain);
        }

        final List<FieldInfoDomain> fieldList = oracleManager.getFieldList("", "JIANGBO.test_view");
        for (FieldInfoDomain fieldInfoDomain : fieldList) {
            System.out.println(fieldInfoDomain);
        }

        final PreviewDataDomain previewDataDomain = oracleManager.previewData("JIANGBO.test2");
        System.out.println(previewDataDomain);

        final List<String> schemaList = oracleManager.getSchemaList();
        schemaList.forEach(System.out::println);
    }
}
